package com.stuOperate;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.bean.Student;

import DataBase.DataBase;

public class stuOperate extends DataBase{
	
	Statement sta=null;
	ResultSet rs=null;
	int Page;
	int Row;
	public List<Student> getAllStu(int Page,int Row){
		List<Student> list = new ArrayList<Student>(); 
		getcon();
		Student stu=null;
		String sql="select * from (select rownum as num,t.* from student t) where num>("+Page+"-1)*"+Row+" and num<="+Page+"*"+Row+" ";
		try {
			sta=con.createStatement();
			rs=sta.executeQuery(sql);
			
			while(rs.next()){
				stu=new Student();
				stu.setSid(rs.getInt("sid"));
				stu.setSname(rs.getString("sname"));
				stu.setSage(rs.getInt("sage"));
				stu.setSdate(rs.getDate("sdate"));
				
				list.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				sta.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public int lastPage(int Row){
		getcon();
		String sql="select count(*) from student";
		int jilu=0;
		int lastPage=0;
		try {
			sta=con.createStatement();
			rs=sta.executeQuery(sql);
			while(rs.next()){
				jilu=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if(jilu%Row==0){
			lastPage=jilu/Row;
		}else{
			lastPage=jilu/Row+1;
		}
		return lastPage;
	}
	
	public List<Student> getoneStu(int sid){
		List<Student> list=new ArrayList<Student>();
		Student stu =null;
		getcon();
		String sql="select * from student where sid="+sid+"";
		try {
			sta=con.createStatement();
			rs=sta.executeQuery(sql);
			while(rs.next()){
				stu=new Student();
				stu.setSid(rs.getInt("sid"));
				stu.setSname(rs.getString("sname"));
				stu.setSage(rs.getInt("sage"));
				stu.setSdate(rs.getDate("sdate"));
				list.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				sta.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public void addStu(int sid,String sname,int sage,String sdate){
		getcon();
		String sql="insert into student(sid,sname,sage,sdate) values("+sid+",'"+sname+"',"+sage+",to_date('"+sdate+"','yyyy-mm-dd'))";
		try {
			sta=con.createStatement();
			sta.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				sta.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	public void upStu(int sid,String sname,int sage,String sdate){
		
		getcon();
		String sql="update student set sname='"+sname+"',sage="+sage+",sdate=to_date('"+sdate+"','yyyy-mm-dd') where sid="+sid+"";
		try {
			sta=con.createStatement();
			sta.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				sta.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
	
	public void delStu(int sid){
		getcon();
		String sql="delete from student where sid="+sid+"";
		try {
			sta=con.createStatement();
			sta.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				sta.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	
}
